﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using Common;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace DAL
{
    public class ODSConfigDAL : DB
    {
        public ODSConfigDAL(LoginInfo info)
        {
            base.loginInfo = info;
        }

        #region "添加ODS_Config/ODS_STG_Config/ODS_StgColumns/ODS_Stg_On_Condition"
        public bool Update(ODSConfigTransData data)
        {
            //创建DbCommand对象
            HIDB.dbo con = base.conn();
            con.TranBegin();
            try
            {
                AfterUpdate(data.ODSConfig.GUID);
                SqlParameter[] sqlparams = new SqlParameter[] { 
                    new SqlParameter("@GUID", data.ODSConfig.GUID) 
                };
                #region "添加或更新ODSConfig表中的数据"
                if (!this.Update(data.ODSConfig, con))
                {
                    RollBack(data.ODSConfig.GUID);
                    con.TranRollback();
                    return false;
                }
                string jobStepID = Guid.NewGuid().ToString();
                ETLSYSJobConfigStep jobStep = new ETLSYSJobConfigStep();
                jobStep.GUID = jobStepID;
                jobStep.JobID = EnumConvert.ConvertJopTypeToGUID(PublicEnum.JobType.TransferData);
                jobStep.StepID = data.ODSConfig.GUID;
                jobStep.StepName = "执行加工模型数据源[" + data.ODSConfig.GUID + "]";
                jobStep.StepDescription = "执行加工数据源[" + data.ODSConfig.GUID + "]";
                jobStep.StepType = "";
                jobStep.StepStatus = 1;

                ETLSYSJobConfigStepDAL etlSYSJobConfigStepDAL = new DAL.ETLSYSJobConfigStepDAL(loginInfo);
                etlSYSJobConfigStepDAL.Add(jobStep); 
                #endregion
                 
                #region "向ODSSTGConfig表中添加数据"
                List<string> odsNames = new List<string>();
                ODSSTGConfigDAL odsSTGConfigDAL = new ODSSTGConfigDAL(loginInfo);
                ETLSYSJobConfigStepPredecessorsDAL etlPredecessorsDAL = new ETLSYSJobConfigStepPredecessorsDAL(loginInfo);
                foreach (ODSSTGConfig item in data.ODSSTGConfigList)
                {
                    item.GUID = Guid.NewGuid().ToString();
                    item.ODS_ID = data.ODSConfig.GUID;
                    odsNames.Add(item.SourceSysName+":"+item.STG_DisplayName);
                    if (!odsSTGConfigDAL.Add(item, con))
                    {
                        RollBack(data.ODSConfig.GUID);
                        con.TranRollback();
                        return false;
                    }

                    ETLSYSJobConfigStepPredecessors jobStepPredecessors = new ETLSYSJobConfigStepPredecessors();
                    jobStepPredecessors.GUID = Guid.NewGuid().ToString();
                    jobStepPredecessors.StepID = item.StgID;
                    jobStepPredecessors.StepParentID = jobStepID;

                    etlPredecessorsDAL.Add(jobStepPredecessors);
                }
                #endregion

                #region "准备ODSStgColumns表的数据"
                IList<ODSStgColumns> ODSStgColumnsMasterData = new List<ODSStgColumns>();
                foreach (ODSSTGConfigTransData item in data.ODSSTGConfigTransDataList)
                {
                    ODSStgColumns leftModel = new ODSStgColumns();
                    leftModel.GUID = Guid.NewGuid().ToString();
                    leftModel.Stg_ID = data.ODSSTGConfigList.Where(p => p.STG_DisplayName.Equals(item.LeftTableName)).FirstOrDefault().GUID;
                    leftModel.ColumnName = item.LeftColumnsName;
                    ODSStgColumnsMasterData.Add(leftModel);

                    ODSStgColumns rightModel = new ODSStgColumns();
                    rightModel.GUID = Guid.NewGuid().ToString();
                    rightModel.Stg_ID = data.ODSSTGConfigList.Where(p => p.STG_DisplayName.Equals(item.RightTableName)).FirstOrDefault().GUID;
                    rightModel.ColumnName = item.RightColumnsName;
                    ODSStgColumnsMasterData.Add(rightModel);
                }
                #endregion

                #region "向ODSStgColumns表中添加数据"
                ODSStgColumnsDAL odsStgColumnsDAL = new ODSStgColumnsDAL(loginInfo);
                foreach (ODSStgColumns item in ODSStgColumnsMasterData)
                {
                    if (!odsStgColumnsDAL.Add(item, con))
                    {
                        RollBack(data.ODSConfig.GUID);
                        con.TranRollback();
                        return false;
                    }
                }
                #endregion

                #region"准备ODSStgOnCondition表的数据"
                IList<ODSStgOnCondition> ODSStgOnConditionMasterData = new List<ODSStgOnCondition>();
                foreach (ODSSTGConfigTransData item in data.ODSSTGConfigTransDataList)
                {
                    ODSStgOnCondition model = new ODSStgOnCondition();

                    model.GUID = Guid.NewGuid().ToString();
                    string leftTableStgID = data.ODSSTGConfigList.Where(p => p.STG_DisplayName.Equals(item.LeftTableName)).FirstOrDefault().GUID;
                    model.Column_ID_Left = ODSStgColumnsMasterData.Where(p => p.ColumnName.Equals(item.LeftColumnsName) && p.Stg_ID.Equals(leftTableStgID)).FirstOrDefault().GUID;

                    string rightTableStgID = data.ODSSTGConfigList.Where(p => p.STG_DisplayName.Equals(item.RightTableName)).FirstOrDefault().GUID;
                    model.Column_ID_Right = ODSStgColumnsMasterData.Where(p => p.ColumnName.Equals(item.RightColumnsName) && p.Stg_ID.Equals(rightTableStgID)).FirstOrDefault().GUID;
                    model.ODS_Id = data.ODSConfig.GUID;
                    ODSStgOnConditionMasterData.Add(model);
                }
                #endregion

                #region "向ODSStgOnCondition表中添加数据"
                ODSStgOnConditionDAL odsStgOnConditionDAL = new ODSStgOnConditionDAL(loginInfo);
                foreach (ODSStgOnCondition item in ODSStgOnConditionMasterData)
                {
                    if (!odsStgOnConditionDAL.Add(item, con))
                    {
                        RollBack(data.ODSConfig.GUID);
                        con.TranRollback();
                        return false;
                    }
                }
                #endregion
                #region "为ods_config表生成脚本并且更新到表中"
                if (!CreateScipts(data.ODSConfig.GUID, con))
                {
                    RollBack(data.ODSConfig.GUID);
                    con.TranRollback();
                    return false;
                }
                //执行脚本
                //DataTable dt = con.StoredDataTable("[ODS].[FillOdsDataFromStaging_db]", new SqlParameter[] { new SqlParameter("@ODS_ID", data.ODSConfig.GUID) });
                //int retVal = int.Parse(dt.Rows[0][0].ToString());
                //if (retVal == 0)
                //{
                //    RollBack(data.ODSConfig.GUID);
                //    con.TranRollback();
                //    return false;
                //}
                #endregion
                //自动生成数据质量
                con.StoredUpdate("[Working_db].[UP_AutoCreateDQ]", new SqlParameter[] { new SqlParameter("@ODS_ID", data.ODSConfig.GUID) });

                
//                DataTable dqDt = con.GetDataTable("select guid from [Working_db].[DQ_Config] WHERE ModelID=@GUID", sqlparams);
//                if (dqDt == null || dqDt.Rows.Count == 0)
//                {
//                    con.ExecuteSql(@"INSERT INTO [Working_db].[DQ_Config]([GUID],[ModelID],[DQ_Name],[DQ_Description],[CreateTime],[IsDelete]) 
//                                    VALUES(NEWID(),@GUID,'自动生成数据质量_1',@DQ_Description,GETDATE(),0)", sqlparams);
//                }
                con.ExecuteSql("Update [Working_db].[Model_Config] set Step=2 where GUID=@GUID and Step=1", sqlparams);
                con.TranCommit();
                return true;
            }
            catch (Exception ex)
            {
                con.TranRollback();
                RollBack(data.ODSConfig.GUID);
                return false;
                throw ex;
            }
        }

        public bool AddAutoDQ(Staging_dbTable ostg,string modelID)
        {
            try
            {
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[] { 
                    new SqlParameter("@ModelID", modelID) 
                    ,new SqlParameter("@stgtblname", ostg.ViewName) 
                    ,new SqlParameter("@stgName", ostg.DisplayName) 
                };
                con.StoredUpdate("[Working_db].[AddAutoDQ]", sqlparams);
                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }
        private void RollBack(string guid)
        {
            try
            {
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@GUID", guid) };
                con.StoredUpdate("[Working_db].[UP_ODSConfigRollback]", sqlparams);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool InitDQAndModel(string modelID)
        {
            try
            {
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@ModelID", modelID) };
                con.ExecuteSql(@"
 delete from [Working_db].[DQ_Rules_Config] where DQ_ID in (select guid from [Working_db].[DQ_Config] where ModelID=@ModelID);
 delete from [Working_db].[DQ_Config] where ModelID=@ModelID;
 delete from [Working_db].[Model_Column_SourceConfig] where ModelColumnID in (select GUID from [Working_db].[Model_ColumnConfig] where ModelTableID=@ModelID);
 delete from [Working_db].[Model_ColumnConfig] where ModelTableID=@ModelID;
 delete from [Working_db].[Model_Column_ExtendConfig] where ModelTableID=@ModelID;", sqlparams);
                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        private void AfterUpdate(string id)
        {
            try
            {
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@ODS_ID", id) };
                con.StoredUpdate("[Working_db].[UP_ODS_Config_AfterUpdate]", sqlparams);
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion

        #region "为ods_config表生成脚本并且更新到表中"
        private bool CreateScipts(string odsId, HIDB.dbo conn)
        {
            bool result = true;
            try
            {
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@ods_id", odsId) };
                conn.StoredUpdate("[Working_db].[CreateOdsTb_FromStgTb]", sqlparams);
            }
            catch (Exception)
            {
                result = false;
                throw;
            }
            return result;
        }
        #endregion

        #region "添加ODSConfig"
        private bool Add(ODSConfig data, HIDB.dbo con)
        {
            bool result = true;
            //创建数据库对象
            try
            {
                //添加输入输出参数
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new SqlParameter("@GUID",data.GUID)
                    ,new SqlParameter("@ODS_TableName",data.ODS_TableName)
                    ,new SqlParameter("@ODS_Descript_tbCHName",data.ODS_Descript_tbCHName)
                    ,new SqlParameter("@ODS_Descript_tbCHInfo",data.ODS_Descript_tbCHInfo)
                    ,new SqlParameter("@CreateUser",data.CreateUser)
                 };
                con.StoredUpdate("[Working_db].[UP_ODS_Config_Insert]", sqlparams);
            }
            catch (Exception ex)
            {
                result = false;
                Logger.LogError("[Working_db].[UP_ODS_Config_Insert]", "[Working_db].[UP_ODS_Config_Insert]", 0, 1, ex, "", "");
            }
            return result;
        }
        #endregion

        #region "更新ODSConfig"
        private bool Update(ODSConfig data, HIDB.dbo con)
        {
            bool result = true;
            //创建数据库对象
            try
            {
                //添加输入输出参数
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new SqlParameter("@GUID",data.GUID) 
                };
                con.ExecuteSql("Insert into [Working_db].[ODS_Config](GUID,ODS_TableName,CreateTime) values(@GUID,@GUID,getdate())", sqlparams);
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;
        }
        #endregion

        #region "查询ODSConfig列表"
        /// <summary>
        /// 获取ODS_Config对象列表
        /// </summary>
        public IList<ODSConfig> GetList(string tableName, int pageSize, int pageIndex)
        {
            IList<ODSConfig> result = new List<ODSConfig>();
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                //添加输入输出参数
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new SqlParameter("@tableName",string.IsNullOrEmpty(tableName)?DBNull.Value.ToString():tableName)
                    ,new SqlParameter("@pageSize",pageSize)
                    ,new SqlParameter("@pageIndex",pageIndex)
                };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_ODS_Config_GetList]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    ODSConfig model = null;
                    foreach (DataRow row in dt.Rows)
                    {
                        model = new ODSConfig();
                        model.GUID = row["GUID"].ToString();
                        model.ODS_TableName = row["ODS_TableName"].ToString();
                        model.ODS_Descript_tbCHName = row["ODS_Descript_tbCHName"].ToString();

                        result.Add(model);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

        /// <summary>
        /// 获取ODS_Config对象总数
        /// </summary>
        public int GetListCount(string tableName)
        {
            int result = 0;
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@tableName", string.IsNullOrEmpty(tableName) ? DBNull.Value.ToString() : tableName) };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_ODS_Config_GetListCount]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    result = Int32.Parse(dt.Rows[0]["RowsCount"].ToString());
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }
        #endregion

        #region"删除ODSConfig"
        /// <summary>
        /// 删除ODSConfig
        /// </summary>
        /// <param name="id">主键ID</param>
        /// <returns>0删除成功 1：关联DQ无法删除 2：关联model无法删除 3：同时关联DQ和Model无法删除</returns>
        public int Delete(string id)
        {
            int result = -1;
            try
            {
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new SqlParameter("@GUID",id)
                };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_ODS_Config_Delete]", sqlparams);
                int hasDQ = int.Parse(dt.Rows[0][0].ToString());
                int hasModel = int.Parse(dt.Rows[0][1].ToString());
                if (hasDQ > 0 && hasModel > 0)
                {
                    result = 3;
                }
                else if (hasDQ == 0 && hasModel > 0)
                {
                    result = 2;
                }
                else if (hasDQ > 0 && hasModel == 0)
                {
                    result = 1;
                }
                else
                {
                    result = 0;
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }
        #endregion

        #region "检查ODSConfig表名是否存在"
        public bool ODSTableNameIsExist(string tableName, string odsid)
        {
            bool result = false;
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@ODS_TableName",tableName)
                    ,new  SqlParameter("@GUID",string.IsNullOrEmpty(odsid)?DBNull.Value.ToString():odsid)
                };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_ODS_Config_TableName_IsExist]", sqlparams);
                result = Int32.Parse(dt.Rows[0]["RowsCount"].ToString()) > 0 ? true : false;
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }
        #endregion

        #region "查询ODS_Config/ODS_STG_Config/ODS_StgColumns/ODS_Stg_On_Condition"
        public ODSConfigTransData GetItem(string odsId)
        {
            ODSConfigTransData result = new ODSConfigTransData();
            try
            {
                HIDB.dbo con = base.conn();
                DataTable dt = new DataTable();
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@ODS_ID", odsId) };
                ////查询ODS_Config信息
                //dt = con.StoredDataTable("[Working_db].[UP_ODS_Config_GetItem]", sqlparams);
                //if (dt != null && dt.Rows.Count > 0)
                //{
                //    ODSConfig model = null;
                //    foreach (DataRow row in dt.Rows)
                //    {
                //        model = new ODSConfig();
                //        model.GUID = row["GUID"].ToString();
                //        model.ODS_TableName = row["ODS_TableName"].ToString();
                //        model.ODS_Descript_tbCHName = row["ODS_Descript_tbCHName"].ToString();
                //        model.ODS_Descript_tbCHInfo = row["ODS_Descript_tbCHInfo"].ToString();
                //    }
                //    result.ODSConfig = model;
                //}
                //查询ODS_STG_Config/ODS_StgColumns/ODS_Stg_On_Condition信息
                //dt = con.StoredDataTable("[Working_db].[UP_ODS_Config_GetItemExtend]", sqlparams);
                //if (dt != null && dt.Rows.Count > 0)
                //{
                //    IList<ODSSTGConfig> data1 = new List<ODSSTGConfig>();
                //    ODSSTGConfig model1 = null;

                //    foreach (DataRow row in dt.Rows)
                //    {
                //        model1 = new ODSSTGConfig();
                //        model1.STG_TB_Name = row["STG_TB_Name"].ToString();
                //        data1.Add(model1);
                //    }
                //    result.ODSSTGConfigList = data1;
                //}

                dt = con.StoredDataTable("[Working_db].[UP_ODS_Config_GetItemOnCondition]", sqlparams);

                if (dt != null && dt.Rows.Count > 0)
                {
                    IList<OnConditionTemp> listTemp = new List<OnConditionTemp>();
                    OnConditionTemp tempModel = null;
                    foreach (DataRow item in dt.Rows)
                    {
                        tempModel = new OnConditionTemp();
                        tempModel.GUID = item["GUID"].ToString();
                        tempModel.TableName = item["TableName"].ToString();
                        tempModel.ColumnName = item["ColumnName"].ToString();
                        tempModel.Direction = int.Parse(item["Direction"].ToString());

                        listTemp.Add(tempModel);
                    }

                    IList<ODSSTGConfigTransData> data2 = new List<ODSSTGConfigTransData>();
                    var leftTableList = listTemp.Where(p => p.Direction == 1).ToList();
                    OnConditionTemp rightTable = null;
                    ODSSTGConfigTransData model2 = null;
                    foreach (var item in leftTableList)
                    {
                        model2 = new ODSSTGConfigTransData();
                        rightTable = listTemp.Where(p => p.Direction == 2 && p.GUID.Equals(item.GUID)).FirstOrDefault();

                        model2.LeftTableName = item.TableName;
                        model2.LeftColumnsName = item.ColumnName;
                        model2.RightTableName = rightTable.TableName;
                        model2.RightColumnsName = rightTable.ColumnName;
                        model2.Operator = "=";
                        model2.GUID = item.GUID;

                        data2.Add(model2);
                    }
                    result.ODSSTGConfigTransDataList = data2;
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }
        #endregion

        public IList<string> GetODSTableNameList()
        {
            IList<string> result = new List<string>();
            HIDB.dbo con = base.conn();
            DataTable dt = con.GetDataTable("SELECT  [ODS_TableName] FROM [Working_db].[ODS_Config] order by ODS_TableName");
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    result.Add(item[0].ToString());
                }
            }
            return result;
        }
    }

    public class OnConditionTemp
    {
        public string GUID { get; set; }
        public string TableName { get; set; }
        public string ColumnName { get; set; }
        public int Direction { get; set; }
    }
}
